package com.roc.service.impl;

import java.io.File;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


import com.roc.dao.DaoException;
import com.roc.dao.ISQLBaseDao;
import com.roc.newPage.IPageDao;
import com.roc.pojo.Operators;
import com.roc.service.ServiceException;
import com.roc.service.revisedata.ReviseDataService;
import com.roc.util.Constance;
import com.roc.util.constant.ConfigProperties;
import com.roc.util.constant.Role;
@Service
@Transactional
public class ReviseDataServiceImpl implements ReviseDataService{

static Logger logger=Logger.getLogger(ReviseDataServiceImpl.class);
    
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	@Autowired
	 private IPageDao pageDao;

	
	
	@Override
	public Map<String, Object> importMidList(File file, Operators op) throws ServiceException {
		// TODO Auto-generated method stub
		 Map<String, String> Province = ConfigProperties.getGBProviceProperties();
		Map<Integer,List<Object>> uploadData=new LinkedHashMap<Integer, List<Object>>();//存放上传数据
		
		
		Map<String,Object> resultMap=new LinkedHashMap<String, Object>();//上传文件错误原因及定位
		int res_count=0;
		int hang=1;
		try{		
		//上传Excel文件流转换为Map集合
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, (LinkedHashMap<Integer, List<Object>>) uploadData,(LinkedHashMap<String, Object>) resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		logger.info("开始进行数据保存操作");	
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();	
		//封装数据
		it=uploadData.entrySet().iterator(); 
		
		Map<String,Object> successMap = new LinkedHashMap<String,Object>();
		List<List<Object>> res = new ArrayList<List<Object>>();
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			StringBuffer sb = new StringBuffer();
			List<Object> resultData = new ArrayList<Object>();
			try {
			//根据商户号查询所需信息
				sb = sb.append("  SELECT rownum,a.MCHT_CD ,b.TERM_ID,a.NAME,a.NAME_BUSI,a.UC_BC_CD_32,a.MCC_CD_18, " )
					   .append(" '' 交易,c.real_name, " )
					   .append(" (select op.REAL_NAME from t_operators op where op.id=c.COMPANYID ) companyName "
					   		+ ",a.BUSI_LICE_NO,a.CERTIF,a.PROV_CD,a.CITY_CD,a.AREA_CD,a.REG_ADDR, " )
					   .append("  e.name yu, a.CONTACT_NAME,a.CONTACT_PHONENO,b.REC_CRT_TS, (case a.status when '03' then '是' else '否' end ) Status , " )
					   .append("  (case a.status  when '03' then a.REC_UPD_TS   end )  logout_date, " )
					   .append(" (select op.real_name from  t_operators  op where  op.id= c.oneagentid ) oneName, a.BANK_BELONG_CD  " )
					   .append("  FROM  tbl_mcht_inf a  " )
					   .append("  left join  tbl_term_inf b on b.mcht_cd=a.mcht_cd " )
					   .append("  left join  t_operators c on c.id=a.ext1 ")
					   //.append("  left join  TBL_EDIT_MCHT_INF d on a.busi_rang=d.mcht_cd ")
					   .append("  left join tbl_dictionarylayeritem e on e.dic_code=(substr (a.UC_BC_CD_32,5,8) )")
					   .append("  where e.dic_type='UnionPay_Area' and  b.mcht_cd=? ");
			
				 Object[] parmas={list.get(0).toString().trim()};
				 List<Object> name_list=pageDao.querySqlAll(sb.toString(), parmas);
				 
				 //查询近3个月是否有交易
		     	 int count_list =WhetherDeal(list.get(0).toString());	
				 String isBusi="";
				 if(count_list>0){isBusi="是";}else{isBusi="否";}
				
				  if(name_list.isEmpty())
				  {
					  resultData.add(hang);
					  resultData.add(list.get(0));
					  resultData.add("商户号不存在或不属于当前登录人员");
					  res.add(resultData);
					  hang++;
					  res_count++;
					  continue;
				  }	
	           	  for (Object obj : name_list) {
	           		resultData = new ArrayList<Object>();
	           		Object[] ob = (Object[])obj;
	           	    resultData.add(hang);
	           	    resultData.add(ob[1]);
	                resultData.add(ob[2]);
	                resultData.add(ob[3]);
	                resultData.add(ob[4]);
	                resultData.add(ob[5]);
	                resultData.add(ob[6]);
	                //resultData.add(ob[7]);
	                resultData.add(isBusi);//判断近三个月是否交易
	                resultData.add(ob[8]); //直属代理商
	                resultData.add(ob[22]);//一级代理商
	                resultData.add(ob[23]);//所属机构
	                resultData.add(ob[9]);
	                resultData.add(ob[10]);
	                resultData.add(ob[11]);
	                resultData.add(Province.get(ob[12]));//省
	                Map<String, String> city = 
							ConfigProperties.getGBCityProperties(Constance.Null2EmptyTrim(ob[12]));
	                resultData.add(city.get(ob[13]));//市
	                Map<String,String> area = 
	                		ConfigProperties.getGBAreaProperties(Constance.Null2EmptyTrim(ob[13]));
	                resultData.add(area.get(ob[14]));//县
	                resultData.add(ob[15]);
	               /* Map<String,String> yu = ConfigProperties.getUnionProviceProperties();
	                resultData.add(yu.get(ob[16]));*/
	                resultData.add(ob[16]);
	                resultData.add(ob[17]);
	                resultData.add(ob[18]);
	                String applDate =Constance.Null2EmptyTrim(ob[19]);
	                if(!"".equals(applDate)&&applDate!=null){
	                	resultData.add(applDate.substring(0, 10));
	                } else resultData.add(ob[19]);
	               /* String status1 = Constance.Null2EmptyTrim(ob[20]);
					if(status1.length()<2){
						 resultData.add("未知");
					}else if("1".equals(status1.substring(0, 1))){
						 resultData.add("正常");
					}else if("2".equals(status1.substring(0, 1))){
						 resultData.add("注销");
					}else if("3".equals(status1.substring(0, 1))){
						 resultData.add("冻结");
					}
					resultData.add(status1);*/
	                resultData.add(ob[20]);
	                String dongjie =Constance.Null2EmptyTrim(ob[21]);
	                if(dongjie!=null&&!"".equals(dongjie)){
	                	
	                	resultData.add(dongjie.substring(0, 10));	
	                }
	                else resultData.add(ob[21]);
	                
	                res.add(resultData);
	               
	                hang++;
	           	  }	
	           	 res_count++;
			} catch (DaoException e) {
				resultData.add(list.get(0));
				resultData.add("异常情况发生，该条商户查询失败");
				 
				logger.error("商户号导入失败",e);
			}
		}
			logger.info("商户号导入成功");
			
			successMap.put("count", resultMap.get("count"));
    		successMap.put("data", res);
    	return successMap;
	}catch(Exception e){
		logger.debug(e.getMessage(), e);
		throw new ServiceException("批量导入商户号失败");
	}
		
}

	
	private Object SubString(Object object, int i, int j) {
		// TODO Auto-generated method stub
		return null;
	}


	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,LinkedHashMap<Integer,List<Object>> map,LinkedHashMap<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			//存在的商户号条数aa
		 int aa =trLength-1;
			resultMap.put("count", aa);
//			resultMap.put("count", 14);
			if(trLength>2000){
				logger.info("上传文件行数超出了限制条数：【"+trLength+"条】");
				resultMap.put("result", "上传的商户号不能大于2000条");
				return false;
			}
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<1){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>1){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号"};
			String titleName=null;
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
				titleName=row.getCell(0).getStringCellValue();
				if(!titleName.equals(title[0]))
				{
					reason_error="第1行第1列的列名不正确,正确的列名为【"+title[0]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常："+ e.getMessage());
			return false;
		}
		return true;
	}
	
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{1,15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度不能超过15，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	
     /**
      * 查询近3个月是否有交易
      * @param mchtCd
      * @return
      * @throws ServiceException
      */
	public int WhetherDeal(String mchtCd) throws ServiceException {
		String sql ="select count(1)" 
					+"from  apstfr.tbl_tfr_his_trn_log ap,tbl_mcht_inf a "
					+"where ap.trans_st='1000000000' and ap.resp_cd='00' and ap.ma_trans_cd in ('1011','1171')"
					+"and a.mcht_cd=ap.mchnt_cd  and a.mcht_cd =? "
					+"and ap.rec_crt_ts>sysdate-90  ";
		int count=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(mchtCd);
		try {
			count = pageDao.countByParams(sql, parmas);
			if(count>0){
				logger.info("查询该商户号【"+mchtCd+"】近三个月交易次数【"+count+"】次");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询商户号交易次数验证失败！");
		}
		
		return count;
   }
	
}
